package com.steven.team.dao

import com.steven.team.pojo.*
import org.apache.ibatis.annotations.*
import org.springframework.stereotype.Repository

@Repository
interface FriendDao {

    @Select("SELECT id,friendId,uname,iconUrl from chat_friends LEFT JOIN user_detail on friendId = user_detail.uid where chat_friends.uid=#{uid} order by convert(uname using gbk)")
    fun queryFriends(uid: Long): List<Friend>

    @Select("SELECT\n" +
            "\ta.id,a.applicantUser,a.toUser,uname,iconUrl,a.agree,a.createTime \n" +
            "FROM\n" +
            "\t( SELECT id,applicantUser,toUser,agree,createTime FROM chat_friend_application WHERE chat_friend_application.toUser = #{uid} ) a\n" +
            "\tLEFT JOIN user_detail ON a.applicantUser = user_detail.uid")
    fun queryApplicationToSelf(uid: Long): List<ApplyFriend>

    @Select("SELECT\n" +
            "\ta.id,a.applicantUser,a.toUser,uname,iconUrl,a.agree,a.createTime \n" +
            "FROM\n" +
            "\t( SELECT id,applicantUser,toUser,agree,createTime FROM chat_friend_application WHERE chat_friend_application.toUser = #{uid} and toUserStatus=0 ) a\n" +
            "\tLEFT JOIN user_detail ON a.applicantUser = user_detail.uid")
    fun queryApplicationToSelfUnRead(uid: Long): List<ApplyFriend>

    @Select("SELECT\n" +
            "\ta.id,a.applicantUser,a.toUser,uname,iconUrl,a.agree,a.createTime \n" +
            "FROM\n" +
            "\t( SELECT id, applicantUser,toUser,agree,createTime FROM chat_friend_application WHERE chat_friend_application.applicantUser=#{uid}) a\n" +
            "\tLEFT JOIN user_detail ON a.toUser = user_detail.uid")
    fun querySelfApplication(uid: Long): List<ApplyFriend>

    @Select("SELECT\n" +
            "\ta.id,a.applicantUser,uname,iconUrl,a.createTime \n" +
            "FROM\n" +
            "\t( SELECT id, applicantUser,createTime FROM chat_friend_application WHERE chat_friend_application.id = #{id} ) a\n" +
            "\tLEFT JOIN user_detail ON a.applicantUser = user_detail.uid order by createTime desc")
    fun queryApplyFriendsById(id: Long): List<ApplyFriend>

    @Insert("INSERT INTO chat_friend_application(applicantUser,toUser,createTime) " +
            "VALUE(#{apply.applicantUser},#{apply.toUser},#{apply.createTime}) " +
            "ON DUPLICATE KEY UPDATE createTime = #{apply.createTime}," +
            "agree = NULL," +
            "toUserStatus = FALSE")
    @Options(useGeneratedKeys = true, keyProperty = "apply.id", keyColumn = "id")
    fun applyFriendRelationship(@Param("apply") apply: ApplyFriend): Long

    @Insert("INSERT INTO chat_friends(uid,friendId,createTime) VALUES(#{uid},#{friendId},#{createTime}),(#{friendId},#{uid},#{createTime}) ON DUPLICATE KEY UPDATE createTime = #{createTime}")
    fun addFriendRelationship(uid: Long, friendId: Long, createTime: Long = System.currentTimeMillis()): Int

    //更新同意或者添加好友
    @Update("update chat_friend_application set agree=#{agree} where applicantUser=#{applicantUser} and toUser=#{toUser}")
    fun updateFriendship(applicantUser: Long, toUser: Long, agree: Boolean): Int

    //被申请人的已读状态
    @Update("update chat_friend_application set toUserStatus=true where toUser=#{toUser}")
    fun updateToUserApplicantReadStatus(toUser: Long): Int

    @Delete("delete from chat_friends where uid=#{uid} and friendId=#{friendId}")
    fun deleteFriend(uid: Long, friendId: Long): Int

    @Select("select uid,uname,school,college,judge,startYear,userType,iconUrl from user_detail where uid = #{uid}")
    fun queryUserDetail(uid: Long): List<UserDetail>

    @Select("select *\n" +
            "from chat_friends\n" +
            "where uid = #{uid} and friendId = #{friendId}\n" +
            "   or uid = #{friendId} and friendId = #{uid}")
    fun query2UserIsFriends(uid: Long,friendId: Long): List<IsFriend>

    @Select("select user sendUser, max(distinct createTime) createTime, iconUrl sendUserIcon, uname sendUserName\n" +
            "from (select user, createTime, iconUrl, uname\n" +
            "      from (\n" +
            "               select sendUser user, max(distinct createTime) createTime\n" +
            "               from chat_message\n" +
            "               where receiveUser = #{uid}\n" +
            "               group by user\n" +
            "               union\n" +
            "               select receiveUser user, max(distinct createTime) createTime\n" +
            "               from chat_message\n" +
            "               where sendUser = #{uid}\n" +
            "               group by user\n" +
            "           ) as a\n" +
            "               LEFT JOIN user_detail ON a.user = user_detail.uid) as b\n" +
            "group by user, iconUrl, uname\n" +
            "order by createTime desc,convert(sendUser using gbk)")
    fun queryChatFriends(uid: Long): List<ChatFriendHistory>
}